from sqlalchemy import select, or_, and_,func,desc,update,delete
from sqlalchemy.orm import  Session
from school_models import Student, StudentGrade, Course, SexEnum

from db_engine import engine_future as engine

class MySqlOrmTest(object):
    # 方式一：实例化Session类
    # from sqlalchemy.orm import  Session
    # with Session(engine) as session:
    #     session.add(user_obj)
    #     session.commit()

    # 方式二：使用工厂函数创建
    # from sqlalchemy.orm import  sessionmaker
    # Session = sessionmaker(engine)
    # with Session.begin() as session:
    #     session.add(user_obj)


    def __init__(self):
        # v2.0风格
        self.session = Session(bind=engine,future=True)

# 查询学习内容
# 1）ORM查询基础
# 2）条件查询
    def exercise_search_1(self):
        # 查询年龄大于12岁的所有学生
        stmt = select(Student).where(Student.age>12)
        queryset = self.session.execute(stmt).scalars()
        for item in queryset:
            print(item)

    def exercise_search_2(self):
        # 查询年龄在9——12岁之间（含）的学生信息
        # stmt = select(Student).where(Student.age.between(9,12))
        stmt = select(Student).filter(Student.age.between(9,12))
        queryset = self.session.execute(stmt).scalars()
        for item in queryset:
            print(item)
    def exercise_search_3(self):
        # 查询已设置/未设置年龄的学生信息
        # stmt = select(Student).where(Student.age.is_(None))
        stmt = select(Student).where(Student.age.is_not(None))
        queryset = self.session.execute(stmt).scalars()
        for item in queryset:
            print(item)
    def exercise_search_4(self):
        # 查询学生年龄在9岁和12岁的学生信息
        # stmt = select(Student).where(Student.age.in_((9,12)))
        stmt = select(Student).where(or_(
            Student.age == 9,
            Student.age == 12
        ))
        queryset = self.session.execute(stmt).scalars()
        for item in queryset:
            print(item)

    def exercise_search_5(self):
        # 查询所有姓’李‘的学生信息
        # stmt = select(Student).where(Student.stu_name.like('王%'))
        # stmt = select(Student).where(Student.stu_name.ilike('王%'))
        stmt = select(Student).where(Student.stu_name.startswith('王'))
        # queryset = self.session.execute(stmt).scalars()
        queryset = self.session.execute(stmt).scalars().all()
        for item in queryset:
            print(item)

    def exercise_search_6(self):
        # 查询所有姓’李‘，名只有一个字的学生信息
        stmt = select(Student).where(Student.stu_name.like('王_'))
        queryset = self.session.execute(stmt).scalars().all()
        for item in queryset:
            print(item)

    def exercise_search_7(self):
        # 查询所有姓名包含’雪‘字的学生信息
        # stmt = select(Student).where(Student.stu_name.like('%雪%'))
        stmt = select(Student).where(Student.stu_name.contains('雪'))
        queryset = self.session.execute(stmt).scalars().all()
        for item in queryset:
            print(item)

    def exercise_search_8(self):
        # 查询所有年龄在9岁到12岁之间的女生
        # stmt = select(Student).where(Student.age.between(9,18)).where(Student.sex == SexEnum.男.value)
        # stmt = select(Student).where(and_(
        #     Student.age.between(9, 18),
        #     Student.sex == SexEnum.男.value
        # ))
        stmt = select(Student).where(and_(
            Student.age >= 9,
            Student.age <= 18,
            Student.sex == SexEnum.男.value
        ))
        queryset = self.session.execute(stmt).scalars().all()
        for item in queryset:
            print(item,item.sex.name)

    def exercise_search_9(self):
        # 查询所有12岁以上的男生和9岁以下的女生
        stmt = select(Student).where(
            or_(
                and_(
                    Student.age > 12,
                    Student.sex == SexEnum.男.value
                ),
                and_(
                    Student.age < 9,
                    Student.sex == SexEnum.女.value
                )
            )
        )
        queryset = self.session.execute(stmt).scalars().all()
        for item in queryset:
            print(item, item.sex.name)

    def exercise_search_10(self):
        # 查询所有'李'姓的女生中年龄不为空的学生信息
        stmt = select(Student).where(and_(
            Student.stu_name.startswith('李'),
            Student.sex == SexEnum.女.value,
            Student.age.is_not(None)

        ))
        queryset = self.session.execute(stmt).scalars().all()
        for item in queryset:
            print(item, item.sex.name)

# 3）函数的使用
    def exercise_function_11(self):
        # 统计本班的学生人数
        # 方式一：func.count()
        # stmt = select(func.count()).select_from(Student)
        # row = self.session.execute(stmt).first()
        # print(row,row[0])

        # row = self.session.execute(stmt).scalar_one()
        # print(row)

        # 指定别名stu_count
        # stmt = select(func.count().label('stu_count')).select_from(Student)
        # row = self.session.execute(stmt).mappings().one()
        # print(row,row['stu_count'])


        # 方式二：session.query()
        # count = self.session.query(Student).count()
        # count = self.session.query(Student).filter(Student.age > 10).count()
        count = self.session.query(Student).filter_by(age = 18).count()
        print(count)

    def exercise_function_12(self):
        # 统计本班语文成绩的最高分/最低分
        stmt = select(func.max(StudentGrade.score).label('max_score'),func.min(StudentGrade.score).label('min_score')).where(StudentGrade.course_id == 1)
        # result = self.session.execute(stmt).one()
        result = self.session.execute(stmt).mappings().one()
        print(result)

    def exercise_function_13(self):
        # 统计本班语文成绩的平均分
        stmt = select(func.avg(StudentGrade.score).label('avg_score')).where(StudentGrade.course_id == 1)
        # result = self.session.execute(stmt).one()
        result = self.session.execute(stmt).mappings().one()
        print(result)

    def exercise_function_14(self):
        # 统计学生张三的各科总分
        stmt = select(func.sum(StudentGrade.score).label('sum_score')).where(StudentGrade.student_id == 1)
        # result = self.session.execute(stmt).one()
        result = self.session.execute(stmt).mappings().one()
        print(result)
# 4）多表连接
    def exercise_table_join_15(self):
        # 查询每个学生的学号、姓名、年龄、课程、老师、成绩
        # 方式一：使用mappings
        # stmt = select(Student,Course,StudentGrade)\
        #     .where(Student.id == StudentGrade.student_id)\
        #     .where(Course.id == StudentGrade.course_id)
        # queryset = self.session.execute(stmt).mappings().all()
        # # print(queryset)
        # for item in queryset:
        #     print(item.Student.stu_no,item.Student.stu_name,item.Student.age,item.Course.course_name,item.Course.teacher,item.StudentGrade.score)

        # 方式二：使用scalars
        stmt = select(StudentGrade) \
            .where(Student.id == StudentGrade.student_id) \
            .where(Course.id == StudentGrade.course_id)
        queryset = self.session.execute(stmt).scalars().all()
        # print(queryset)
        for item in queryset:
            print(item.student.stu_no, item.student.stu_name, item.student.age, item.course.course_name,
                  item.course.teacher, item.score)

# 5）分组与去重
    def exercise_distinct(self):
        # 查找所有地址信息（去重）
        stmt = select(Student.address).distinct()
        result = self.session.execute(stmt).scalars().all()
        for item in result:
            print(item)

    def exercise_group_1(self):
        # 统计每个班的学生人数
        stmt = select(Student.class_name,func.count().label('stu_count')).group_by(Student.class_name)
        queryset = self.session.execute(stmt).mappings().all()
        for item in queryset:
            print(item)

    def exercise_group_2(self):
        # 统计每个班男生、女生的总人数
        stmt = select(Student.class_name,Student.sex,func.count().label('stu_count')).group_by(Student.class_name,Student.sex)
        queryset = self.session.execute(stmt).mappings().all()
        for item in queryset:
            print(item)

    def exercise_group_3(self):
        # 查找三门成绩都及格（>=60）的学生
        # 学生及格的科目有几个，=3
        stmt = select(StudentGrade,func.count().label('course_count'))\
            .where(StudentGrade.score >= 60)\
            .group_by(StudentGrade.student_id).having(func.count() == 3)
        queryset = self.session.execute(stmt).mappings().all()
        for item in queryset:
            print("查询",item)

# 6）排序
    def exercise_order_1(self):
        # 将三年级所有学生的语文成绩按照从高到低进行排序
        stmt = select(StudentGrade).\
            where(StudentGrade.student_id == Student.id).\
            where(StudentGrade.course_id == Course.id).\
            where(Course.course_name == '语文').\
            order_by(StudentGrade.score.desc())
        # stmt = select(StudentGrade). \
        #     where(StudentGrade.student_id == Student.id). \
        #     where(StudentGrade.course_id == Course.id). \
        #     order_by(StudentGrade.score.desc())
        queryset = self.session.execute(stmt).scalars().all()
        # queryset = self.session.execute(stmt).mappings().all()
        for item in queryset:
            print(item.student.stu_no,item.student.stu_name,item.score)
            # print(item.StudentGrade) #mappings的打印


    def exercise_order_2(self):
        # 将三年级所有学生的期末考试总成绩按照从高到低进行排序
        stmt = select(Student,func.sum(StudentGrade.score).label('score_sum'))\
            .where(Student.id == StudentGrade.student_id)\
            .group_by(Student.id)\
            .order_by(desc('score_sum'))
        queryset = self.session.execute(stmt).mappings().all()
        for item in queryset:
            print(item)
# 7）分页处理
    def exercise_paginate_1(self):
        # 三年级所有学生中语文成绩最好的前10名
        stmt = select(StudentGrade)\
            .where(StudentGrade.student_id == Student.id)\
            .where(StudentGrade.course_id == Course.id)\
            .where(Course.course_name == '语文')\
            .order_by(StudentGrade.score.desc())\
            .limit(10)
        queryset = self.session.execute(stmt).scalars().all()
        for item in queryset:
            print(item.student.stu_no,item.student.stu_name,item.score)

    def exercise_paginate_2(self):
        # 三年级所有学生中期末考试总成绩的第11--第20名
        stmt = select(Student,func.sum(StudentGrade.score).label('score_sum'))\
            .where(Student.id == StudentGrade.student_id)\
            .group_by(Student.id)\
            .order_by(desc('score_sum'))\
            .limit(10)\
            .offset(10)
        queryset = self.session.execute(stmt).mappings().all()
        for item in queryset:
            print(item)
    def exercise_paginate_2_1(self,page=1,page_size=10):
        # 三年级所有学生中期末考试总成绩的第11--第20名
        stmt = select(Student,func.sum(StudentGrade.score).label('score_sum'))\
            .where(Student.id == StudentGrade.student_id)\
            .group_by(Student.id)\
            .order_by(desc('score_sum'))\
            .limit(page_size)\
            .offset((page-1)*page_size)
        queryset = self.session.execute(stmt).mappings().all()
        for item in queryset:
            print(item)

    def exercise_paginate_3(self,page=1,page_size=15):
        # 0,15
        # 15,30
        start = (page-1) * page_size
        end = page * page_size
        queryset = self.session.query(Student).slice(start,end)
        for item in queryset:
            print(item)


# 修改数据
    def update_one_data(self):
        # 修改单条数据
        stu_obj = self.session.get(Student,2)
        stu_obj.phone_no = '15888888888'
        self.session.add(stu_obj)
        self.session.commit()

    def update_more_data(self):
        # 批量修改数据
        # stmt = update(Student)\
        #     .where(Student.id <= 2)\
        #     .values(age=Student.age + 1)
        # result = self.session.execute(stmt)

        stmt = update(Student) \
            .where(Student.stu_name.startswith('王')) \
            .values(age=Student.age + 1)\
            .execution_options(synchronize_session='fetch')
        result = self.session.execute(stmt)
        self.session.commit()
        print('行数：',result.rowcount)


# 删除数据
    def delete_one_data(self):
        # 删除一条数据
        # 删除学生成绩
        # grade_obj = self.session.get(StudentGrade,7)
        # self.session.delete(grade_obj)
        # self.session.commit()

        # 删除学生信息
        # 删除学生信息的同时  需要把学生关联的学生成绩表的信息一并删除
        # 需要再school models设计时  添加这些内容
        # ForeignKey('parent.id',ondelete='CASCADE')
        # relationship(cascade="all,delete")
        grade_obj = self.session.get(Student, 9)
        self.session.delete(grade_obj)
        self.session.commit()

    def delete_more_data(self):
        # 删除多条数据
        stmt = delete(StudentGrade).where(StudentGrade.score < 60)
        result = self.session.execute(stmt)
        self.session.commit()
        print("删除的行数：",result.rowcount)

    def close(self):
        try:
            self.session.close()
        except:
            pass

def main():
    obj = MySqlOrmTest()
    # obj.exercise_search_1()
    # obj.exercise_search_2()
    # obj.exercise_search_3()
    # obj.exercise_search_4()
    # obj.exercise_search_5()
    # obj.exercise_search_6()
    # obj.exercise_search_7()
    # obj.exercise_search_8()
    # obj.exercise_search_9()
    # obj.exercise_search_10()
    # obj.exercise_function_11()
    # obj.exercise_function_12()
    # obj.exercise_function_13()
    # obj.exercise_function_14()
    # obj.exercise_table_join_15()
    # obj.exercise_distinct()
    # obj.exercise_group_1()
    # obj.exercise_group_2()
    # obj.exercise_group_3()
    # obj.exercise_order_1()
    # obj.exercise_order_2()
    # obj.exercise_paginate_1()
    # obj.exercise_paginate_2()
    # obj.exercise_paginate_2_1()
    # obj.exercise_paginate_2_1(page=2)
    # obj.exercise_paginate_3()
    # obj.exercise_paginate_3(page=1)


    # obj.update_one_data()
    # obj.update_more_data()


    # obj.delete_one_data()
    obj.delete_more_data()


    obj.close()

if __name__ == '__main__':
    main()